﻿using System;
using System.Data;
using SemiCASE.DataAccess;
using SemiCASE.DataType.Common.Attributes;

namespace SemiCASE.Business
{
    [ServiceConnectionNameAttribute("SemiCASEConnectionString")]
    public class ProjectBS : BusinessBase
    {
        public DataTable LoadProjects()
        {
            DataTable dt = new DataTable();
            IData data = GetDataObject();
            string sqlText = @"SELECT 
	                                P.ProjectID
                                    , P.Name
                                    , CONVERT(VARCHAR(11), P.StartDate, 104) AS StartDate
                                    , CONVERT(VARCHAR(11), P.EndDate, 104) AS EndDate
                                    , P.StatusID
                                    , S.StatusName
                                    , '' AS SiraNo 
                                FROM SemiCaseDB.dbo.PROJECT AS P 
	                                 INNER JOIN SemiCaseDB.dbo.PROJECT_STATUS AS S ON P.StatusID = S.StatusID";

            data.GetRecords(dt, sqlText);
            return dt;
        }

        public DataTable LoadStatus()
        {
            DataTable dt = new DataTable();
            IData data = GetDataObject();
            string sqlText = @"SELECT 
	                              StatusID
                                  , StatusName
                               FROM SemiCaseDB.dbo.PROJECT_STATUS";

            data.GetRecords(dt, sqlText);
            return dt;
        }

        public void DeleteProject(Guid pProjectID)
        {
            IData data = GetDataObject();
            string sqlText = @"DELETE FROM SemiCaseDB.dbo.PROJECT
                               WHERE ProjectID = @ProjectID";

            data.AddSqlParameter("ProjectID", pProjectID, SqlDbType.UniqueIdentifier);
            data.ExecuteStatement(sqlText);
        }

        public void CreateProject(Guid pProjectID, string pName, DateTime pStartDate, DateTime pEndDate, int pStatus)
        {
            IData data = GetDataObject();
            string sqlText = @"INSERT INTO SemiCaseDB.dbo.PROJECT
                               VALUES(@ProjectID, @Name, @StartDate, @EndDate, @StatusID)";

            data.AddSqlParameter("ProjectID", pProjectID, SqlDbType.UniqueIdentifier);
            data.AddSqlParameter("Name", pName, SqlDbType.VarChar);
            data.AddSqlParameter("StartDate", pStartDate, SqlDbType.DateTime);
            data.AddSqlParameter("EndDate", pEndDate, SqlDbType.DateTime);
            data.AddSqlParameter("StatusID", pStatus, SqlDbType.Int);
            data.ExecuteStatement(sqlText);
        }

        public void UpdateProject(Guid pProjectID, string pName, DateTime pStartDate, DateTime pEndDate, int pStatus)
        {
            IData data = GetDataObject();
            string sqlText = @"UPDATE SemiCaseDB.dbo.PROJECT
                                SET Name = @Name
	                                  , StartDate = @StartDate
	                                  , EndDate = @EndDate
	                                  , StatusID = @StatusID
                                 WHERE ProjectID = @ProjectID";

            data.AddSqlParameter("ProjectID", pProjectID, SqlDbType.UniqueIdentifier);
            data.AddSqlParameter("Name", pName, SqlDbType.VarChar);
            data.AddSqlParameter("StartDate", pStartDate, SqlDbType.DateTime);
            data.AddSqlParameter("EndDate", pEndDate, SqlDbType.DateTime);
            data.AddSqlParameter("StatusID", pStatus, SqlDbType.Int);
            data.ExecuteStatement(sqlText);
        }

        public DataTable SelectProjectInfo(Guid pProjectID)
        {
            DataTable dt = new DataTable();
            IData data = GetDataObject();
            string sqlText = @"SELECT 
	                                P.ProjectID
                                    , P.Name
                                    , P.StartDate
                                    , P.EndDate
                                    , P.StatusID
                                    , S.StatusName
                                FROM SemiCaseDB.dbo.PROJECT AS P 
	                                 INNER JOIN SemiCaseDB.dbo.PROJECT_STATUS AS S ON P.StatusID = S.StatusID
                                WHERE P.ProjectID = @ProjectID";

            data.AddSqlParameter("ProjectID", pProjectID, SqlDbType.UniqueIdentifier);
            data.GetRecords(dt, sqlText);
            return dt;
        }
    }
}
